﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataBaseLibrary.DBInterfaces;

namespace DataBaseLibrary.DBObjectsFB
{

    public class UserFdb : User
    {
        public UserFdb(IDBProvider provider, string userName, string password) : base(provider, userName, password)
        {
        }

        protected override void GetUserFromID(object ID)
        {
            String SQLQuery = String.Format("select USER_NAME, PASSWORD, PRIVELEGIES from USER_DOMAIN_NEW where ID = '{0}'", ID);
            
            try
            {
                List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
                if (rows.Count > 1 || rows.Count == 0)
                    throw new Exception("Нет такого человека!");

                 UserName = rows[0][0].ToString();
                 Password = rows[0][1].ToString();
                 _Privelegies = (Privelegies)rows[0][2];
            }
            catch (Exception)
            {
                throw new Exception("Нет такого человека!");
            }            
        }

        protected override bool AddUserToDB(string md5Password)
        {
            try
            {

                String SQLQuery =
                    String.Format(
                        "insert into USER_DOMAIN_NEW (ID, USER_NAME, PASSWORD, PRIVILEGIES) values (0, '{0}', '{1}', {2});",
                        UserName, md5Password, (int) _Privelegies);
                Provider.ExecQueryDml(SQLQuery);
            }
            catch (Exception err)
            {
                throw new Exception(err.Message);
                return false;
            }
            return true;
        }

        protected override bool DeleteUserFromDB(object ID)
        {
            try
            {

                String SQLQuery =
                    String.Format("delete from USER_DOMAIN_NEW where ID = '{0}';", ID);
                Provider.ExecQueryDml(SQLQuery);
            }
            catch (Exception err)
            {
                throw new Exception(err.Message);
                return false;
            }
            return true;
            
        }

        protected override string GetPasswordFromUser(string UserName)
        {
            String SQLQuery = String.Format("select PASSWORD from USER_DOMAIN_NEW where USER_NAME = '{0}'", UserName);
            String md5HashPasswd = null;
            try
            {
                List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
                if (rows.Count > 1 || rows.Count == 0)
                    return null;

                return rows[0][0].ToString();
            }
            catch (Exception)
            {
                md5HashPasswd = null;
            }
            return md5HashPasswd;
        }

        protected override Privelegies GetPrivelegiesFromDB()
        {
            String SQLQuery = String.Format("select PRIVILEGIES from USER_DOMAIN_NEW where USER_NAME = '{0}'", UserName);
            
            try
            {
                List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
                if (rows.Count > 1 || rows.Count == 0)
                    throw new Exception("Нет такой записи!");

                return (Privelegies)rows[0][0];
            }
            catch (Exception)
            {
                throw new Exception("Нет такой записи!");
            }
            
        }

        public override object GetUserId()
        {
            String SQLQuery = String.Format("select ID  from USER_DOMAIN_NEW where USER_NAME = '{0}'", UserName);

            try
            {
                List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
                if (rows.Count > 1 || rows.Count == 0)
                    throw new Exception("Нет такого человека!");
                return rows[0][0];
            }
            catch (Exception)
            {
                throw new Exception("Нет такого человека!");
            }  
        }
    }


    /// <summary>
    /// Класс описывает бизнес-логику БД приложения
    /// </summary>
    public class FDBLogic : IDBLogic
    {
        public FDBLogic(IDBProvider provider) : base(provider)
        {}

        public override ResultFields SearchIndividualsFio(Individual Individual)
        {

            ResultFields resultFields = new ResultFields();
            string SQLQuery;
            if (Individual != null)
                SQLQuery = String.Format("select ID , LAST_NAME, FIRST_NAME, PATRONYMIC, DATE_OF_BIRTH from INDIVIDUAL_TABLE where LAST_NAME = '{0}'", Individual.LastName);
            else
                SQLQuery = String.Format("select ID , LAST_NAME, FIRST_NAME, PATRONYMIC, DATE_OF_BIRTH from INDIVIDUAL_TABLE");
            List<object[]> rows = Provider.ExecQueryRead(SQLQuery);

            resultFields.NamesFields = new string[]{"ID", "Фамилия", "Имя", "Отчество", "Дата рождения"};
            resultFields.ValueFields = rows;

            return resultFields;
        }

        public override ResultFields SearchSalesContract(Individual Individual)
        {
            ResultFields resultFields = new ResultFields();
            if (Individual == null)
                Individual = new Individual();

            string SQLQuery =
                String.Format(
                    "select distinct  sale_contract_table.id, sale_contract_table.date_create_sale,  individual_table.last_name, individual_table.first_name, individual_table.patronymic, vehicle_table.vin, sale_contract_table.price from sale_contract_table " +
                    "inner join buyers_table on sale_contract_table.id_buyer = buyers_table.id " +
                    "inner join sallers_table on sale_contract_table.id_saler = sallers_table.id " +
                    "inner join vehicle_table on sale_contract_table.id_vehicle = vehicle_table.id " +
                    "inner join individual_table on sallers_table.id_individual_table = individual_table.id " +
                    "or  buyers_table.id_individual_table = individual_table.id where " +
                    "lower(individual_table.last_name) containing lower('{0}') " +
                    "and " +
                    "lower(individual_table.first_name) containing lower('{1}') " +
                    "and " +
                    "lower(individual_table.patronymic) containing lower('{2}') " +
                    "and " +
                    "lower(individual_table.passport_series) containing lower('{3}') " +
                    "and " +
                    "lower(individual_table.passport_number) containing lower('{4}') ",
                    Individual.LastName, Individual.FirstName, Individual.Patronymic, Individual.PassportNumber, Individual.PassportSeries );

            List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
            resultFields.NamesFields = new string[] { "№ Договора", "Дата создания", "Фамилия", "Имя", "Отчество", "VIN", "Стоимость" };
            resultFields.ValueFields = rows;

            return resultFields;

        }

        public override ResultFields SearchDeclaration(Individual Individual)
        {
            ResultFields resultFields = new ResultFields();
            if (Individual == null)
                Individual = new Individual();

            string SQLQuery =
                String.Format(
                    "select application_table.id, application_table.date_create,  individual_table.last_name, individual_table.first_name, individual_table.patronymic, vehicle_table.vin  from application_table " +
                    "inner join vehicle_table on application_table.id_vechicle = vehicle_table.id " +
                    "inner join representative_table on application_table.id_representative = representative_table.id " +
                    "inner join individual_table on application_table.id_owner = individual_table.id " +
                    "or  representative_table.id_individual = individual_table.id where " +
                    "lower(individual_table.last_name) containing lower('{0}') " +
                    "and " +
                    "lower(individual_table.first_name) containing lower('{1}') " +
                    "and " +
                    "lower(individual_table.patronymic) containing lower('{2}') " +
                    "and " +
                    "lower(individual_table.passport_series) containing lower('{3}') " +
                    "and " +
                    "lower(individual_table.passport_number) containing lower('{4}')",


                    Individual.LastName, Individual.FirstName, Individual.Patronymic, Individual.PassportNumber,
                    Individual.PassportSeries);

            List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
            resultFields.NamesFields = new string[]
            {"№ Заявления", "Дата создания", "Фамилия", "Имя", "Отчество", "VIN"};
            resultFields.ValueFields = rows;

            return resultFields;
        }


        public override ResultFields SearchDeclarationVu(Individual Individual)
        {
            ResultFields resultFields = new ResultFields();
            if (Individual == null)
                Individual = new Individual();

            string SQLQuery =
                String.Format(

                    "select application_vu_table.id, application_vu_table.date_create, individual_table.last_name, individual_table.first_name, individual_table.patronymic, application_vu_table.motive_delivery from application_vu_table " +
                    "inner join individual_table on application_vu_table.id_owner = individual_table.id where " +
                    "lower(individual_table.last_name) containing lower('{0}') " +
                    "and " +
                    "lower(individual_table.first_name) containing lower('{1}') " +
                    "and " +
                    "lower(individual_table.patronymic) containing lower('{2}') " +
                    "and " +
                    "lower(individual_table.passport_series) containing lower('{3}') " +
                    "and " +
                    "lower(individual_table.passport_number) containing lower('{4}')",


                    Individual.LastName, Individual.FirstName, Individual.Patronymic, Individual.PassportNumber,
                    Individual.PassportSeries);

            List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
            resultFields.NamesFields = new string[]
            {"№ Заявления", "Дата создания", "Фамилия", "Имя", "Отчество", "Причина"};
            resultFields.ValueFields = rows;

            return resultFields;
        }

        protected override ResultFields GetAllUsersFromDB()
        {
            ResultFields resultFields = new ResultFields();


            string SQLQuery =
                String.Format("select ID, USER_NAME, PRIVILEGIES from USER_DOMAIN_NEW");

            List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
            resultFields.NamesFields = new string[] { "ID", "Имя пользователя", "Привелегии"};
            resultFields.ValueFields = rows;

            return resultFields;
        }

        protected override ResultFields GetAllDocumentsHystoryFromDB(DateTime dateStart, DateTime dateStop)
        {
            ResultFields resultFields = new ResultFields();


            string SQLQuery =
                String.Format("select NUMBER_DOCUMENT, TYPE_DOCUMENT, DATE_CRE_CHAN, ACTION, USER_DOMAIN_NEW.USER_NAME from DOCUMENTS_HISTORY_TABLE  inner join USER_DOMAIN_NEW on DOCUMENTS_HISTORY_TABLE.id_user = USER_DOMAIN_NEW.ID where DOCUMENTS_HISTORY_TABLE.DATE_CRE_CHAN >= '{0}' and DOCUMENTS_HISTORY_TABLE.DATE_CRE_CHAN <= '{1}'", dateStart.ToString("d"), dateStop.ToString("d"));

            List<object[]> rows = Provider.ExecQueryRead(SQLQuery);
            resultFields.NamesFields = new string[] {"Номер документа", "Тип документа", "Дата создания/изменения", "Действия", "Имя пользователя" };
            resultFields.ValueFields = rows;

            return resultFields;
        }

        public override void AddDocumentHistory(User cuurentUser, string NumberDocument, string TypeDocument, string Action, DateTime date, Double price)
        {
            try
            {

                String SQLQuery =
                    String.Format(
                        "insert into DOCUMENTS_HISTORY_TABLE (NUMBER_DOCUMENT, TYPE_DOCUMENT, ACTION, ID_USER, DATE_CRE_CHAN, PRICE) values ('{0}', '{1}', '{2}', {3}, '{4}', {5});",
                        NumberDocument, TypeDocument,  Action, cuurentUser.GetUserId(), date.ToString("d"), price);
                Provider.ExecQueryDml(SQLQuery);
            }
            catch (Exception err)
            {
                throw new Exception(err.Message);
                
            }            
        }
    }
}
